const connection = require('../../sql');

class DeptService {
	// 创建部门
	async createDept(params) {
		const { pid, name, sort, remark, leaders, create_id } = params;

		// 计算新部门的 deep
		let deep = 1;
		if (pid !== 0) {
			const [parent] = await connection.execute('SELECT deep FROM sys_dept WHERE id = ? AND del_flag = "F"', [pid]);
			if (parent.length === 0) {
				throw new Error('父部门不存在或已被删除');
			}
			deep = parent[0].deep ? parent[0].deep + 1 : 1;
		}

		// 插入部门基本信息
		const deptStatement = `
		  INSERT INTO sys_dept (pid, name, deep, sort, remark, create_id, create_time)
		  VALUES (?, ?, ?, ?, ?, ?, ?)
		`;
		const deptParams = [pid, name, deep, sort || null, remark || null, create_id, new Date()];
		const [deptResult] = await connection.execute(deptStatement, deptParams);
		const newDeptId = deptResult.insertId;

		// 更新闭包表
		await this.updateDeptClosure(newDeptId, pid);

		// 更新父部门的 has_children
		if (pid !== 0) {
			await connection.execute('UPDATE sys_dept SET has_children = "T" WHERE id = ? AND del_flag = "F"', [pid]);
		}

		// 插入负责人到 sys_dept_leader
		if (leaders && leaders.length > 0) {
			const values = leaders.map((leaderId) => [newDeptId, leaderId]);
			const leaderStatement = `
			INSERT INTO sys_dept_leader (dept_id, leader_id)
			VALUES ?
		  `;
			await connection.query(leaderStatement, [values]);
		}

		return { id: newDeptId };
	}

	// 更新部门闭包表
	async updateDeptClosure(deptId, pid) {
		const selfStatement = `
      INSERT INTO sys_dept_closure (ancestor_id, descendant_id, depth)
      VALUES (?, ?, 0)
    `;
		await connection.execute(selfStatement, [deptId, deptId]);

		if (pid !== 0) {
			const parentStatement = `
        INSERT INTO sys_dept_closure (ancestor_id, descendant_id, depth)
        SELECT ancestor_id, ?, depth + 1
        FROM sys_dept_closure
        WHERE descendant_id = ?
      `;
			await connection.execute(parentStatement, [deptId, pid]);
		}
	}

	// 获取部门树状列表
	async getDeptTree() {
		const deptStatement = `
		  SELECT 
			id, pid, name, deep, sort, remark, is_lock AS isLock
		  FROM sys_dept
		  WHERE del_flag = 'F'
		  ORDER BY sort ASC, id ASC
		`;
		const [depts] = await connection.execute(deptStatement);

		const leaderStatement = `
		  SELECT 
			dl.dept_id, 
			dl.leader_id AS user_id,
			u.username
		  FROM sys_dept_leader dl
		  INNER JOIN sys_dept d ON dl.dept_id = d.id
		  INNER JOIN sys_user u ON dl.leader_id = u.id
		  WHERE d.del_flag = 'F' AND u.del_flag = 'F'
		`;
		const [leaders] = await connection.execute(leaderStatement);

		const leaderMap = {};
		const leaderInfoMap = {};
		leaders.forEach(({ dept_id, user_id, username }) => {
			const key = Number(dept_id);
			if (!leaderMap[key]) leaderMap[key] = [];
			if (!leaderInfoMap[key]) leaderInfoMap[key] = [];
			leaderMap[key].push(user_id);
			leaderInfoMap[key].push(`${user_id}:${username}`);
		});
		const deptMap = {};
		depts.forEach((dept) => {
			dept.children = [];
			dept.leaders = leaderMap[dept.id] || [];
			dept.leaderInfo = leaderInfoMap[dept.id] ? leaderInfoMap[dept.id].join(',') : '';
			dept.dataScopeCd = '';
			dept.scopeId = '';
			dept.deptIds = [];
			dept.userIds = [];
			deptMap[dept.id] = dept;
		});

		const tree = [];
		depts.forEach((dept) => {
			if (dept.pid === 0) {
				tree.push(dept);
			} else if (deptMap[dept.pid]) {
				deptMap[dept.pid].children.push(dept);
			}
		});

		return tree;
	}

	// 更新部门
	async updateDept(id, params) {
		const { pid, name, deep, sort, remark, isLock, leaders, update_id } = params;

		const deptStatement = `
      UPDATE sys_dept
      SET pid = ?, name = ?, deep = ?, sort = ?, remark = ?, is_lock = ?, update_id = ?, update_time = ?
      WHERE id = ? AND del_flag = 'F'
    `;
		const [deptResult] = await connection.execute(deptStatement, [
			pid,
			name,
			deep || null,
			sort || null,
			remark || null,
			isLock || 'F',
			update_id,
			new Date(),
			id,
		]);
		if (deptResult.affectedRows === 0) throw new Error('部门不存在或已被删除');

		await connection.execute('DELETE FROM sys_dept_closure WHERE descendant_id = ?', [id]);
		await this.updateDeptClosure(id, pid);

		if (pid !== 0) {
			await connection.execute('UPDATE sys_dept SET has_children = "T" WHERE id = ? AND del_flag = "F"', [pid]);
		}

		await connection.execute('DELETE FROM sys_dept_leader WHERE dept_id = ?', [id]);
		if (leaders && leaders.length > 0) {
			const leaderStatements = leaders
				.map(
					(leaderId) => `
            INSERT INTO sys_dept_leader (dept_id, leader_id)
            VALUES (${id}, ${leaderId})
          `
				)
				.join(';');
			await connection.query(leaderStatements);
		}

		const [children] = await connection.execute('SELECT COUNT(*) as count FROM sys_dept WHERE pid = ? AND del_flag = "F"', [id]);
		await connection.execute('UPDATE sys_dept SET has_children = ? WHERE id = ?', [children[0].count > 0 ? 'T' : 'F', id]);

		return deptResult;
	}

	// 删除部门
	async deleteDept(ids, userId) {
		const placeholders = ids.map(() => '?').join(',');
		const statement = `
      UPDATE sys_dept
      SET del_flag = 'T', update_id = ?, update_time = ?
      WHERE id IN (${placeholders}) AND del_flag = 'F'
    `;
		const params = [userId, new Date(), ...ids];
		const [result] = await connection.execute(statement, params);
		if (result.affectedRows === 0) throw new Error('没有找到可删除的部门');

		const closureStatement = `
      DELETE FROM sys_dept_closure
      WHERE descendant_id IN (${placeholders})
    `;
		await connection.execute(closureStatement, ids);

		const leaderStatement = `
      DELETE FROM sys_dept_leader
      WHERE dept_id IN (${placeholders})
    `;
		await connection.execute(leaderStatement, ids);

		const [parents] = await connection.execute(`SELECT DISTINCT pid FROM sys_dept WHERE id IN (${placeholders}) AND pid != 0`, ids);
		for (const { pid } of parents) {
			const [children] = await connection.execute('SELECT COUNT(*) as count FROM sys_dept WHERE pid = ? AND del_flag = "F"', [pid]);
			await connection.execute('UPDATE sys_dept SET has_children = ? WHERE id = ?', [children[0].count > 0 ? 'T' : 'F', pid]);
		}

		return result;
	}

	// 获取单个部门信息
	async getDeptById(id) {
		const deptStatement = `
      SELECT 
        id, pid, name, deep, sort, remark, is_lock AS isLock
      FROM sys_dept
      WHERE id = ? AND del_flag = 'F'
    `;
		const [depts] = await connection.execute(deptStatement, [id]);

		if (depts.length === 0) return null;

		const leaderStatement = `
      SELECT leader_id AS user_id
      FROM sys_dept_leader
      WHERE dept_id = ?
    `;
		const [leaders] = await connection.execute(leaderStatement, [id]);

		const dept = depts[0];
		dept.children = ''; // 固定为空字符串，与示例一致
		dept.leaders = leaders.map((leader) => leader.user_id); // 转换为数组
		dept.leaderInfo = '';
		dept.dataScopeCd = '';
		dept.scopeId = '';
		dept.deptIds = [];
		dept.userIds = [];

		return dept;
	}

	//获取上级部门树（下拉框用）
	async getDeptTreeForSelect(excludeNodeId) {
		// 查询所有部门
		let deptStatement = `
      SELECT id, pid, name, deep, sort
      FROM sys_dept
      WHERE del_flag = 'F'
    `;
		let deptParams = [];
		if (excludeNodeId) {
			// 排除指定节点及其子节点
			deptStatement += `
        AND id NOT IN (
          SELECT descendant_id 
          FROM sys_dept_closure 
          WHERE ancestor_id = ?
        )
      `;
			deptParams = [excludeNodeId];
		}
		deptStatement += ' ORDER BY sort ASC, id ASC';
		const [depts] = await connection.execute(deptStatement, deptParams);

		// 构建部门映射
		const deptMap = {};
		depts.forEach((dept) => {
			dept.children = [];
			dept.userTotal = 0; // 暂定为 0，后续可关联用户表计算
			deptMap[dept.id] = dept;
		});

		// 构造树状结构
		const root = {
			id: 0,
			pid: -1,
			deep: '',
			sort: '',
			children: [],
			name: '根部门',
			userTotal: 0,
		};
		deptMap[0] = root;

		depts.forEach((dept) => {
			if (dept.pid === 0) {
				root.children.push(dept);
			} else if (deptMap[dept.pid]) {
				deptMap[dept.pid].children.push(dept);
			}
		});

		return [root];
	}

	// 获取负责人穿梭框-全部用户
	async getDeptLeader({ username, nickname }) {
		let statement = `
      SELECT id, username, nickname
      FROM sys_user
      WHERE del_flag = 'F'
    `;
		const params = [];

		if (username || nickname) {
			statement += ' AND (';
			if (username) {
				statement += 'username LIKE ?';
				params.push(`%${username}%`);
			}
			if (username && nickname) statement += ' OR ';
			if (nickname) {
				statement += 'nickname LIKE ?';
				params.push(`%${nickname}%`);
			}
			statement += ')';
		}

		statement += ' ORDER BY id ASC';
		const [users] = await connection.execute(statement, params);
		return users;
	}
}

module.exports = new DeptService();
